package database;

/**
 * Created with IntelliJ IDEA.
 * User: Wrflohil
 * Date: 12-6-13
 * Time: 12:09
 * To change this template use File | Settings | File Templates.
 */

import webservice.Playlist;
import webservice.Track;

import java.sql.*;

public class DAO implements IDAO {
    private Connection connection = DatabaseConnector.getInstance().getConnection();


    public void insertPlaylist(Playlist playlist){
        int userId = getUserId(playlist.getUserName());
        int playlistId = createPlaylist(userId, playlist.getName());
        for (Track t : playlist.getTrack()){
            int trackId = insertSingleTrack(t.getTitle(), t.getArtists(), t.getAlbum());
            insertSingleTrackInPlaylist(playlistId, trackId);
        }
    }

    public int getUserId(String username){
        int id = 0;
        try {
            PreparedStatement pstmt = connection.prepareStatement("CALL insertUser(?)");
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) id = rs.getInt("@id");
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
            ex.printStackTrace();
        }
        return id;
    }

    public Track selectTrackById(int id) {
        Track track = new Track();
        String query = "SELECT * FROM Track WHERE trackId = "+id;
        try {
            Statement st = connection.createStatement();
            ResultSet rs = st.executeQuery(query);
            while(rs.next()){
                track.setTitle(rs.getString("trackName"));
                track.setArtists(rs.getString("artists"));
                track.setAlbum(rs.getString("album"));
            }

        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
        return track;
    }

    public int createPlaylist(int userid, String playlistname) {
        int id = 0;
        try {
            PreparedStatement pstmt = connection.prepareStatement("CALL createPlaylist(?,?)");
            pstmt.setInt(1, userid);
            pstmt.setString(2, playlistname);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) id = rs.getInt("@id");
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
        return id;
    }

    public void insertSingleTrackInPlaylist(int playlistid, int trackid) {
        try {
            PreparedStatement pstmt = connection.prepareStatement("CALL insertInPlaylist(?,?)");
            pstmt.setInt(1, playlistid);
            pstmt.setInt(2, trackid);
            pstmt.executeQuery();
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    public int insertSingleTrack(String title, String artists, String album) {
        int id = 0;
        try {
            PreparedStatement pstmt = connection.prepareStatement("CALL insertTrack(?,?,?)");
            pstmt.setString(1, title);
            pstmt.setString(2, artists);
            pstmt.setString(3, album);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) id = rs.getInt("@id");
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
        return id;
    }

    public void insertUser(String user) {
        String query = "INSERT INTO USER(userName) VALUES('"+user+"')";
        try {
            Statement st = connection.createStatement();
            st.executeQuery(query);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }
}
